package com.css.bjdt_core.util;

public class ReportConstant {
	//------------------------------------------------------------行列条件------------------------------------------------------------
	//暂时只支持计数
	public static final String PK = "1";
	public static final String FK = "2";
	public static final int STRING = 1;
	public static final int NUMBER = 2;
	public static final int DATE = 3;
	public static final int PROTOTYPE = 4;
	public static final String MASTER_TABLE = "$masterTable$";
	public static final String SUB_TABLE = "$subTable$";
	public static final String MASTER_TABLE_COLUMN = "$masterTableColumn$";
	public static final String SUB_TABLE_COLUMN = "$subTableColumn$";
	public static final String LEFT = "$left$";
	public static final String RIGHT = "$right$";
	public static final String LOGIC_OPERATOR = "$logicOperator$";
	public static final String CONDITION_VAL = "@conditionVal";
	public static final String UNIT_CONDITION_VAL = "$unitConditionVal";
	public static final String START_TIME_CONDITION_VAL = "to_date(@startTimeConditionVal,'yyyy-MM-dd')";
	public static final String END_TIME_CONDITION_VAL = "to_date(@endTimeConditionVal,'yyyy-MM-dd')";
	public static final String RELA_OPERATOR = "$relaOperator$";
	public static final String WHERE_KEY = " where ";
	public static final String AND_KEY = "and ";
	public static final String BASE_SQL = "select count(1) from $masterTable$";
	public static final String JOIN_SQL = " inner join $subTable$ on $subTable$.$subTableColumn$ = $masterTable$.$masterTableColumn$ ";
	public static final String WHERE_SQL = "$left$$masterTable$.$masterTableColumn$ $relaOperator$ @conditionVal $right$$logicOperator$ ";
	//------------------------------------------------------------行列计算------------------------------------------------------------
	public static final String OPERATOR_ARITH = "[\\+\\-\\*/]";
	public static final String OPERATOR_RELA = "[><!=]=?|=";
	public static final String ROW_ZH_CN = "行";
	public static final String COL_ZH_CN = "列";
	public static final String SPLIT_OPERATOR = "_";
	//------------------------------------------------------------指标取值------------------------------------------------------------
	public static final String EXECUTIVE_TABLE_PREFIX = "RPT_CELL_DATA_";
	//连接oracle 判断是否存在表 没有返回"0"
	public static final String ISEXIST_TABLE_SQL = "select count(1) from user_all_tables where table_name = @table_name";
	public static final String ISEXIST_VIEW_SQL = "SELECT count(1) FROM user_views WHERE view_name = @table_name";
	public static final String ISEXIST_FUNCTION_SQL = "select count(1) from user_procedures where object_type='FUNCTION' and object_name = @func_name";
	public static final String ISEXIST_FUNCTIONS_SQL = "SELECT UPPER(object_name) object_name FROM user_procedures WHERE object_type = 'FUNCTION' AND object_name in ( $object_names) group by object_name having count(1) > 0";
	//连接mysql 判断是否存在表 没有返回null
	//public static final String ISEXIST_TABLE_SQL = "SELECT table_name FROM information_schema.TABLES WHERE table_name = @table_name";
	//public static final String ISEXIST_VIEW_SQL = "SELECT table_name FROM information_schema.VIEWS WHERE table_name = @table_name";
	public static final String EXECUTIVE_CREATE_TABLE_SQL = "create table $table_name (CELL_DATA_ID VARCHAR2(50) not null,CELL_ID VARCHAR2(50),CELL_VALUE VARCHAR2(100),HISTORY_CELL_VALUE VARCHAR2(100),CELL_TYPE VARCHAR2(1),ABS_ROW_NUM NUMBER(4),ABS_COL_NUM NUMBER(4),REL_ROW_NUM NUMBER(4),REL_COL_NUM NUMBER(4),DZZ_ID VARCHAR2(50),TEMPLATE_ID VARCHAR2(50),SUM_STATUS VARCHAR2(50),BATCH_ID VARCHAR2(50),INSERT_DATE DATE)";
	public static final String EXECUTIVE_INSERT_SQL = "INSERT INTO $table_name (CELL_DATA_ID,CELL_ID,CELL_VALUE,CELL_TYPE,ABS_ROW_NUM,ABS_COL_NUM,REL_ROW_NUM,REL_COL_NUM,DZZ_ID) VALUES(@CELL_DATA_ID,@CELL_ID,@CELL_VALUE,@CELL_TYPE,@ABS_ROW_NUM,@ABS_COL_NUM,@REL_ROW_NUM,@REL_COL_NUM,@DZZ_ID,@TEMPLATE_ID)";
	public static final String EXECUTIVE_DELETE_SQL = "delete from $table_name where DZZ_ID = @dzz_id";
	//	public static final String EXECUTIVE_DELETE_SQL_BY_BATCHID = "delete from $table_name where BATCH_ID = @batch_id";
	public static final String EXECUTIVE_DELETE_SQL_BY_CELLTYPE = "delete from $table_name where cell_id in (select cell_id from RPT_CELL where cell_type in ('1','3','5')) and DZZ_ID = @dzz_id";
	public static final String EXECUTIVE_QUERY_SQL = "select CELL_DATA_ID,CELL_ID,CELL_VALUE,CELL_TYPE,ABS_ROW_NUM,ABS_COL_NUM,REL_ROW_NUM,REL_COL_NUM,DZZ_ID from $table_name where DZZ_ID = @dzz_id";
	public static final String EXECUTIVE_QUERY_CELL_SQL = "select CELL_DATA_ID,CELL_ID,CELL_VALUE,CELL_TYPE,ABS_ROW_NUM,ABS_COL_NUM,REL_ROW_NUM,REL_COL_NUM,TEMPLATE_ID from $table_name cd where cd.CELL_ID in (@cellIds) and cd.DZZ_ID = @dzz_id";
	public static final String EXECUTIVE_SUM_CELL_SQL = "select sum(cell_value) from $tableName cd where cd.DZZ_ID in (@dzzIds) and cd.CELL_ID = @cellId";
	public static final String TO_DATE_FUN = "to_date('@conditionVal','yyyy/MM/dd')";
	public static final String EXECUTIVE_QUERY_BY_FUNC = "select CELL_ID, CELL_VALUE from Table($funcName(@dzzId, @startTime, @endTime))";
	//------------------------------------------------------------数据校核------------------------------------------------------------
	public static final String VALIDATION_TABLE_PREFIX = "RPT_VALI_INFO_";
	public static final String VALIDATION_CREATE_TABLE_SQL = "create table $table_name (ID VARCHAR2(50) not null,BATCH_ID VARCHAR2(50),SOURCE_EXP VARCHAR2(2000),CELL_EXP VARCHAR2(2000),ACTUAL_EXP VARCHAR2(2000),VALIDATION_TYPE VARCHAR2(500),VALIDATION_STATE VARCHAR2(50),CELL_ID VARCHAR2(50),DZZ_ID VARCHAR2(50),TEMPLATE_ID VARCHAR2(50))";
	public static final String VALIDATION_INSERT_SQL = "INSERT INTO $table_name (ID,BATCH_ID,SOURCE_EXP,CELL_EXP,ACTUAL_EXP,VALIDATION_TYPE,VALIDATION_STATE,CELL_ID,DZZ_ID,TEMPLATE_ID) VALUES(@ID,@BATCH_ID,@SOURCE_EXP,@CELL_EXP,@ACTUAL_EXP,@VALIDATION_TYPE,@VALIDATION_STATE,@CELL_ID,@DZZ_ID,@TEMPLATE_ID)";
	public static final String VALIDATION_DELETE_SQL = "delete from $table_name where DZZ_ID = @dzz_id and TEMPLATE_ID = @template_id";
	public static final String VALIDATION_DELETE_SQL_DZZID = "delete from $table_name where DZZ_ID = @dzz_id";
	public static final String VALIDATION_QUERY_SQL = "select ID,BATCH_ID,SOURCE_EXP,CELL_EXP,ACTUAL_EXP,VALIDATION_TYPE,VALIDATION_STATE,CELL_ID,DZZ_ID from $table_name where DZZ_ID = @dzz_id order by VALIDATION_TYPE asc";
	public static final String VALIDATION_UNKNOW = "0";
	//------------------------------------------------------------报表批注------------------------------------------------------------
	public static final String COMMENT_TABLE_PREFIX = "RPT_CELL_COMM_";
	public static final String COMMENT_CREATE_TABLE_SQL = "create table $table_name (ID VARCHAR2(50) not null,DZZ_ID VARCHAR2(50),TEMPLATE_ID VARCHAR2(50),ABS_ROW_NUM VARCHAR2(10),ABS_COL_NUM VARCHAR2(10),TEMPLATE_COMMENT VARCHAR2(500),SUM_STATUS VARCHAR2(50))";
	public static final String COMMENT_INSERT_SQL = "INSERT INTO $table_name (ID,TEMPLATE_DATA_ID,ABS_ROW_NUM,ABS_COL_NUM,TEMPLATE_COMMENT,SUM_STATUS) VALUES(@ID,@DZZ_ID,@ABS_ROW_NUM,@ABS_COL_NUM,@TEMPLATE_COMMENT,@SUM_STATUS)";
	public static final String COMMENT_DELETE_SQL = "delete from $table_name where TEMPLATE_DATA_ID = @templateDataId";
	public static final String COMMENT_QUERY_SQL = "select ID,DZZ_ID,ABS_ROW_NUM,ABS_COL_NUM,TEMPLATE_COMMENT,SUM_STATUS from $table_name where TEMPLATE_DATA_ID = @templateDataId";
	//------------------------------------------------------------报表审核------------------------------------------------------------
	public static final String EXAMINE_TABLE_PREFIX = "RPT_CELL_EXAM_";
	public static final String EXAMINE_CREATE_TABLE_SQL = "create table $table_name (ID VARCHAR2(50) not null,CELL_ID VARCHAR2(50),TEMPLATE_ID VARCHAR2(50),EXAMINE_INFO VARCHAR2(1000),EXAMINE_DZZID VARCHAR2(50),EXAMINE_TIME VARCHAR2(15),ABS_ROW_NUM VARCHAR2(10),ABS_COL_NUM VARCHAR2(10),REL_ROW_NUM VARCHAR2(10),REL_COL_NUM VARCHAR2(10),BE_AUDITED_DZZID VARCHAR2(50),REPLY_TIME VARCHAR2(15),REPLY_INFO VARCHAR2(1000),ERROR_TYPE CHAR(1),REPLY_STATE CHAR(1))";
	public static final String EXAMINE_INSERT_SQL = "INSERT INTO $table_name (ID,CELL_ID,TEMPLATE_ID,EXAMINE_INFO,EXAMINE_DZZID,EXAMINE_TIME,ABS_ROW_NUM,ABS_COL_NUM,REL_ROW_NUM,REL_COL_NUM,BE_AUDITED_DZZID,REPLY_TIME,ERROR_TYPE,REPLY_STATE) VALUES (@ID,@CELL_ID,@TEMPLATE_ID,@EXAMINE_INFO,@EXAMINE_DZZID,@EXAMINE_TIME,@ABS_ROW_NUM,@ABS_COL_NUM,@REL_ROW_NUM,@REL_COL_NUM,@BE_AUDITED_DZZID,@REPLY_TIME,@ERROR_TYPE,@REPLY_STATE)";
	public static final String EXAMINE_DELETE_SQL = "delete from $table_name where ID = @ID";
	public static final String EXAMINE_QUERY_SQL = "select ID,CELL_ID,TEMPLATE_ID,EXAMINE_INFO,EXAMINE_DZZID,EXAMINE_TIME,ABS_ROW_NUM,ABS_COL_NUM,REL_ROW_NUM,REL_COL_NUM,BE_AUDITED_DZZID,REPLY_TIME,REPLY_INFO,ERROR_TYPE from $table_name where EXAMINE_DZZID = @examineDzzId and TEMPLATE_ID = @templateId";
	public static final String EXAMINE_QUERY_CELL_SQL = "select ID,CELL_ID,TEMPLATE_ID,EXAMINE_INFO,EXAMINE_DZZID,EXAMINE_TIME,ABS_ROW_NUM,ABS_COL_NUM,REL_ROW_NUM,REL_COL_NUM,BE_AUDITED_DZZID,REPLY_TIME,REPLY_INFO,ERROR_TYPE,REPLY_STATE from $table_name where EXAMINE_DZZID = @examineDzzId and TEMPLATE_ID = @templateId and ABS_ROW_NUM = @absRowNum and ABS_COL_NUM = @absColNum order by EXAMINE_TIME desc";
	public static final String EXAMINE_REPLY_QUERY_CELL_SQL = "select ID,CELL_ID,TEMPLATE_ID,EXAMINE_INFO,EXAMINE_DZZID,EXAMINE_TIME,ABS_ROW_NUM,ABS_COL_NUM,REL_ROW_NUM,REL_COL_NUM,BE_AUDITED_DZZID,REPLY_TIME,REPLY_INFO,ERROR_TYPE,REPLY_STATE from $table_name where BE_AUDITED_DZZID = @examineDzzId and TEMPLATE_ID = @templateId and ABS_ROW_NUM = @absRowNum and ABS_COL_NUM = @absColNum order by EXAMINE_TIME desc";
	//public static final String EXAMINE_QUERY_CELL_SQL = "select ID,CELL_ID,TEMPLATE_ID,ERROR_TYPE,EXAMINE_INFO,EXAMINE_UNIT_ID,DATE_FORMAT(EXAMINE_TIME,'%Y-%m-%d %H:%i:%S') as time,TEMPLATE_ID as EXAMINE_TIME,ABS_ROW_NUM,ABS_COL_NUM,TB_DZZ_ID from $table_name where DZZ_ID = @dzzId and TEMPLATE_ID = @TEMPLATE_ID and ABS_ROW_NUM = @absRowNum and ABS_COL_NUM = @absColNum order by EXAMINE_TIME desc";
	//------------------------------------------------------------报表审核记录表------------------------------------------------------------
	public static final String EXAMINE_HISTORY_TABLE_PREFIX = "RPT_HISTORY_";
	public static final String EXAMINE_HISTORY_CREATE_TABLE_SQL = "create table $table_name (ID VARCHAR2(50) not null,CELL_ID VARCHAR2(50),CELL_DATA_ID VARCHAR2(50),CELL_VALUE VARCHAR2(10),HISTORY_RYCELL_VALUE VARCHAR2(50),EXAMINE_TIME DATE,ABS_ROW_NUM VARCHAR2(10),ABS_COL_NUM VARCHAR2(10),REL_ROW_NUM VARCHAR2(10),REL_COL_NUM VARCHAR2(10),DZZ_ID VARCHAR2(50),TEMPLATE_ID VARCHAR2(50),TB_DZZ_ID VARCHAR2(50))";
	public static final String EXAMINE_HISTORY_INSERT_SQL = "INSERT INTO $table_name (ID,CELL_ID,CELL_DATA_ID,CELL_VALUE,HISTORY_RYCELL_VALUE,EXAMINE_TIME,ABS_ROW_NUM,ABS_COL_NUM,REL_ROW_NUM,REL_COL_NUM,DZZ_ID,TEMPLATE_ID,TB_DZZ_ID) VALUES(@ID,@CELL_ID,@CELL_DATA_ID,@CELL_VALUE,@HISTORY_RYCELL_VALUE,@EXAMINE_TIME,@ABS_ROW_NUM,@ABS_COL_NUM,@REL_ROW_NUM,@REL_COL_NUM,@DZZ_ID,@TEMPLATE_ID,@TB_DZZ_ID)";
	//------------------------------------------------------------报表说明------------------------------------------------------------
	public static final String EXPLAIN_TABLE_PREFIX = "RPT_EXPL_DATA_";
	public static final String EXPLAIN_CREATE_TABLE_SQL = "create table $table_name (EXPLAIN_CELL_DATA_ID VARCHAR2(50) not null,EXPLAIN_CELL_VALUE VARCHAR2(50),ABS_ROW_NUM VARCHAR2(50),ABS_COL_NUM VARCHAR2(50),DZZ_ID VARCHAR2(50),EXPLAIN_ID VARCHAR2(50),IS_SAME_ROW VARCHAR2(50),SUM_STATUS VARCHAR2(50))";
	public static final String EXPLAIN_INSERT_SQL = "INSERT INTO $table_name (EXPLAIN_CELL_DATA_ID,EXPLAIN_CELL_VALUE,ABS_ROW_NUM,ABS_COL_NUM,DZZ_ID,EXPLAIN_ID,IS_SAME_ROW,SUM_STATUS) VALUES(@EXPLAIN_CELL_DATA_ID,@EXPLAIN_CELL_VALUE,@ABS_ROW_NUM,@ABS_COL_NUM,@DZZ_ID,@EXPLAIN_ID,@IS_SAME_ROW,@SUM_STATUS)";
	public static final String EXPLAIN_DELETE_SQL = "delete from $table_name where EXPLAIN_ID = @EXPLAIN_ID and DZZ_ID = @DZZ_ID";
	public static final String EXPLAIN_DELETE_SUMMARY_SQL = "delete from $table_name where DZZ_ID = @DZZ_ID and SUM_STATUS = 1";
	public static final String EXPLAIN_QUERY_SQL = "select EXPLAIN_CELL_DATA_ID,EXPLAIN_CELL_VALUE,ABS_ROW_NUM,ABS_COL_NUM,DZZ_ID,EXPLAIN_ID,IS_SAME_ROW,SUM_STATUS from $table_name where EXPLAIN_ID = @EXPLAIN_ID and DZZ_ID = @DZZ_ID and SUM_STATUS is null and ABS_ROW_NUM != 2";
	public static final String EXPLAIN_QUERY_SUMMARY_SQL = "select EXPLAIN_CELL_DATA_ID,EXPLAIN_CELL_VALUE,ABS_ROW_NUM,ABS_COL_NUM,DZZ_ID,EXPLAIN_ID,IS_SAME_ROW,SUM_STATUS from $table_name where EXPLAIN_ID = @EXPLAIN_ID and DZZ_ID = @DZZ_ID and SUM_STATUS = 1 and ABS_ROW_NUM != 2";
	public static final String EXPLAIN_COUNT_SQL = "select count(*) from $table_name where EXPLAIN_ID = @EXPLAIN_ID and DZZ_ID = @DZZ_ID";
	public static final String EXPLAIN_QUERY_BYCOLANDROW_SQL = "select EXPLAIN_CELL_VALUE from $table_name where EXPLAIN_ID = @EXPLAIN_ID and DZZ_ID = @DZZ_ID and ABS_ROW_NUM = @ABS_ROW_NUM and ABS_COL_NUM = @ABS_COL_NUM and SUM_STATUS is null";

	public static final String EXPLAIN_DELETE_SQL_DZZID = "delete from $table_name where DZZ_ID = @DZZ_ID";

	//------------------------------------------------------------报表说明(因为文本型批注，东西太多，所以文本型批注单独存一张表)------------------------------------------------------------
	public static final String EXPLAIN_TEXT_TABLE_PREFIX = "RPT_EXPL_TEXT_";
	public static final String EXPLAIN_TEXT_CREATE_TABLE_SQL = "create table $table_name (EXPLAIN_TEXT_ID VARCHAR2(50) not null,EXPLAIN_TEXT CLOB,EXPLAIN_ID VARCHAR2(50),DZZ_ID VARCHAR2(50))";
	public static final String EXPLAIN_TEXT_INSERT_SQL = "INSERT INTO $table_name (EXPLAIN_TEXT_ID,EXPLAIN_TEXT,EXPLAIN_ID,DZZ_ID) VALUES (@EXPLAIN_TEXT_ID,@EXPLAIN_TEXT,@EXPLAIN_ID,@DZZ_ID)";
	public static final String EXPLAIN_TEXT_DELETE_SQL = "DELETE FROM $table_name WHERE EXPLAIN_ID = @explaInId and DZZ_ID = @dzzId";
	public static final String EXPLAIN_TEXT_QUERY_SQL = "SELECT EXPLAIN_TEXT FROM $table_name WHERE EXPLAIN_TEXT_ID = @explainTextId";

	public static final String EXPLAIN_TEXT_DELETE_SQL_DZZID = "DELETE FROM $table_name WHERE DZZ_ID = @dzzId";
	//------------------------------------------------------------创建一张报表说明和报表文本型批注的视图------------------------------------------------------------
	public static final String EXPLAIN_TEXT_VIEW_PREFIX = "VIEW_EXPL_TEXT_";
	public static final String CREATE_VIEW_EXPLAIN_TEXT_SQL = "CREATE OR REPLACE VIEW VIEW_EXPL_TEXT_$annualId as SELECT t1.*,t2.EXPLAIN_TEXT FROM $tableExpl t1,$tableText t2 where t1.explain_cell_value = t2.explain_text";

	//------------------------------------------------------------报表填报历史------------------------------------------------------------
	public static final String FILL_HISTORY_TABLE_PREFIX = "RPT_FILLHIS_";
	public static final String FILL_HISTORY_CREATE_TABLE_SQL = "create table $table_name (ID VARCHAR2(50) not null,CELL_ID VARCHAR2(50),DZZ_ID VARCHAR2(50),TEMPLATE_ID VARCHAR2(50),CELL_VALUE VARCHAR2(50),HISTORY_CELL_VALUE VARCHAR2(50),UPDATE_TIME VARCHAR2(50),ABS_ROW_NUM VARCHAR2(10),ABS_COL_NUM VARCHAR2(10),REL_ROW_NUM VARCHAR2(10),REL_COL_NUM VARCHAR2(10))";

}
